<?php
	class PO_Model_1 extends CI_Model {
		var $table = "po_";
		function get_barang(){
			$query	=	$this->db->query('select upper(barang.strnama) strnama, barang.intid_barang intid, harga.* from barang 
				left join harga on harga.intid_barang = barang.intid_barang where barang.status_barang = 1 group by barang.intid_barang');
			return $query->result();
		}
		function get_nopo($id,$week,$ket){
			$var = "";
				$query = $this->db->query('select id from counter_ where keterangan = "'.$ket.'"');
				$check0  = $query->result();
				
				$data = array(
					   'id' => $check0[0]->id +1
					);
				$this->db->where('keterangan',$ket);
				$this->db->update('counter_', $data); 
				
				$query = $this->db->query('select id from counter_ where keterangan = "'.$ket.'"');
				$check  = $query->result();
				
				$var = $check[0]->id.'/'.$week.'/'.$id.'/'.strtoupper($ket).'/'.date('m').'/'.date('Y');
				return $var;
			}
		function insert_DataPO($var,$data){
			$table = "";
				if($var == 1){
					$table = "po_";
					}else{
						$table = "po_detail_";
						}
					$this->db->insert($table,$data);
			}
		function DataPO(){
			$query = $this->db->query('select pod.*,
									  b.strnama,
									  (select strnama_cabang 
									   	from cabang c inner join po_ po on po.intid_cabang = c.intid_cabang where po.no_po = pod.no_po group by c.intid_cabang) strnama_cabang,
									  (select time 
									   	from  po_ po where po.no_po = pod.no_po group by po.no_po) timeNow,
									  b.intid_jsatuan
									  from po_detail_ pod inner join barang b on b.intid_barang = pod.intid_barang  
									  where pod.no_po = (select max(po.no_po) no_po
							 			from po_ po) order by b.strnama asc');
			return $query->result();
			}
		function check_po_week($intid_cabang,$intid_week){
			$query2	=	$this->db->query('select count(*) cek from po_ po where po.intid_cabang = "'.$intid_cabang.'" and po.intid_week = "'.$intid_week.'"');
			$cek = $query2->result();
			return $cek[0]->cek;
			}
		function selectWeek(){
			//$thn = date('Y');
			$query = $this->db->query("select intid_week from week where curdate() between dateweek_start and dateweek_end");
			return $query->result();
			}
		function OpenDB($query){
			return $this->db->query($query);
			}
		
		function pengecekanSPKB($no_spkb){
			$query = $this->db->query('select count(*) nomor from spkb where no_spkb = "'.$no_spkb.'"');
			$no = $query->result();
			return $no[0]->nomor;
			}
		///fungsi yang baru
		//line ikhlas 03042013 masih banyak yang diperbaiki
		//pa perlu di buat intquantity_skrg untuk mengurangi penjumalahan sebelumnya
		function getLap_Stok_cab_2($intid_cabang, $week){
	
			$weekbefore = $week-1;
			$query = $this->db->query('select barang.strnama,
									  		barang.intid_barang,
											barang.intid_jsatuan, 
											barang_masuk_before.jum_barang_masuk_before, 
											barang_keluar_before.jum_barang_keluar_before, 
											barang_masuk_after.jum_barang_masuk_after, 
											barang_keluar_after.jum_barang_keluar_after,
											stok_barang.jumlah 
									  from
									  barang left join
									  (select sum(intquantity) jum_barang_keluar_before, nd.intid_barang 
										  from 
										  nota n inner join nota_detail nd on nd.intid_nota = n.intid_nota 
										  where n.intid_cabang = "'.$intid_cabang.'"
										  and n.intid_week <= "'.$weekbefore.'"
										  and n.datetgl > (select date_format(as_of_date,"%Y-%m-%d") from stok_barang limit 0,1) group by nd.intid_barang
										  )as barang_keluar_before
									  on barang_keluar_before.intid_barang = barang.intid_barang
									  left join
									  (select sum(spd.quantity) jum_barang_masuk_before, spd.intid_barang 
									   		from 
											spkb inner join spkb_detail spd on spd.no_spkb = spkb.no_spkb
											where spkb.intid_cabang = "'.$intid_cabang.'"
											and spkb.week <= "'.$weekbefore.'"
											and spkb.tgl_terima > (select date_format(as_of_date,"%Y-%m-%d") from stok_barang where intid_cabang = "'.$intid_cabang.'" limit 0,1) 
											and spkb.no_sj != ""
											and terkirim = 1
											group by spd.intid_barang	
									   	  )as barang_masuk_before
									   on barang_masuk_before.intid_barang = barang.intid_barang
									   left join
									  (select sum(intquantity) jum_barang_keluar_after, nd.intid_barang 
										  from 
										  nota n inner join nota_detail nd on nd.intid_nota = n.intid_nota 
										  where n.intid_cabang = "'.$intid_cabang.'"
										  and n.intid_week = "'.$week.'"
										  and n.datetgl > (select date_format(as_of_date,"%Y-%m-%d") from stok_barang limit 0,1) group by nd.intid_barang
										  )as barang_keluar_after
									  on barang_keluar_after.intid_barang = barang.intid_barang
									  left join
									  (select sum(spd.quantity) jum_barang_masuk_after, spd.intid_barang 
									   		from 
											spkb inner join spkb_detail spd on spd.no_spkb = spkb.no_spkb
											where spkb.intid_cabang = "'.$intid_cabang.'"
											and spkb.week = "'.$week.'"
											and spkb.tgl_terima > (select date_format(as_of_date,"%Y-%m-%d") from stok_barang where intid_cabang = "'.$intid_cabang.'" limit 0,1) 
											and spkb.no_sj != ""
											and terkirim = 1
											group by spd.intid_barang	
									   	  )as barang_masuk_after
									   on barang_masuk_after.intid_barang = barang.intid_barang
									   left join 
									   (select *
											from 
											stok_barang
											where intid_cabang = "'.$intid_cabang.'"
											)as stok_barang
									   on stok_barang.intid_barang = barang.intid_barang
									  order by barang.strnama asc
									  ');
			return $query->result();
		}
		function selectWeek2($intid_cabang)
		{
		$query2 = $this->db->query("select as_of_date from stok_barang where intid_cabang = '".$intid_cabang."' limit 0,1");
		$hasil = $query2->result();
			if(isset($hasil[0]->as_of_date)){
				$temp = $hasil[0]->as_of_date;
			}else{
				$temp = "";
			}
			$query = $this->db->query("select id, intid_week 
									  from week 
									  where dateweek_start > date_format('".$temp."', '%Y-%m-%d')");
			return $query->result();
		}
		//POCO/lookup2
		function get_PO_cabang($intid_cabang){
			$query = $this->db->query("select po.*,po.time waktu,
																		c.strnama_cabang 
																		from po_ po inner join cabang c on c.intid_cabang = po.intid_cabang 
																		where po.intid_cabang = ".$intid_cabang." and
																		po.is_sj = 0
																		order by time desc");
			return $query->result();
		}
		//POCO/SJ_INSERT_SEARCH
		function SPKB_SJ_SEARCH($varcabang,$halaman,$limit){
			$query = $this->db->query('select spd.no_spkb, 
										spd.time waktu,
									  cabang.strnama_cabang 
									  from spkb spd inner join cabang on cabang.intid_cabang = spd.intid_cabang 
									  where (spd.no_sj = "" or spd.no_sj is NULL) 
									  and cabang.strnama_cabang like "'.$varcabang.'"
									  order by spd.time desc
									  limit '.$halaman.','.$limit.' ');
			return $query;
			}
		function SPKB_SJ($halaman,$limit){
			$query = $this->db->query('select spd.no_spkb, spd.time waktu,
									  (select strnama_cabang from cabang where intid_cabang = spd.intid_cabang) strnama_cabang 
									  from spkb spd where (spd.no_sj = "" or spd.no_sj is NULL) order by spd.time desc limit '.$halaman.','.$limit.'');
			return $query;
			}
	}
?>